package Dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Vector;

import Entity.Transfer;

public class SearchDaoHelper {
	public List<Transfer> searchTransfer(String empNumber, String empName,
			String fromLocation, String toLocation, String fromProject,
			String toProject, String fromDept, String toDept)
			throws IOException, ClassNotFoundException, SQLException {
		String sql = "SELECT * FROM Transfer WHERE EmpNumber LIKE ? "
				+ "AND EmpName LIKE ? " + "AND FromLocation LIKE ? "
				+ "AND ToLocation LIKE ? " + "AND FromProject LIKE ? "
				+ "AND ToProject LIKE ? " + "AND FromDept LIKE ? "
				+ "AND ToDept LIKE ?";
		List<Transfer> transferList = new Vector<Transfer>();
		Connection con = DaoUtil.getConnection();
		PreparedStatement stm = con.prepareStatement(sql);
		stm.setString(1, empNumber);
		stm.setString(2, empName);
		stm.setString(3, fromLocation);
		stm.setString(4, toLocation);
		stm.setString(5, fromProject);
		stm.setString(6, toProject);
		stm.setString(7, fromDept);
		stm.setString(8, toDept);
		ResultSet rs = stm.executeQuery();
		while (rs.next()) {
			transferList.add(new Transfer(rs.getString("EmpNumber"), rs
					.getString("EmpName"), rs.getString("FromLocation"), rs
					.getString("ToLocation"), rs.getString("FromProject"), rs
					.getString("ToProject"), rs.getString("FromDept"), rs
					.getString("ToDept"), 0.0));
		}
		DaoUtil.closeConnection(con);
		return transferList;
	}

	public List<String> loadComboBox(String string) throws SQLException,
			ClassNotFoundException, IOException {
		String sql = "select * from " + string;
		Connection con = DaoUtil.getConnection();
		List<String> StringList = new Vector<String>();
		PreparedStatement stm = DaoUtil.getPreparedStatement(con, sql);
		ResultSet rs = stm.executeQuery();
		while (rs.next()) {
			StringList.add(rs.getString("Names"));
		}
		DaoUtil.closeConnection(con);
		return StringList;
	}

	public String getName(String string) throws SQLException,
			ClassNotFoundException, IOException {
		String sql = "SELECT EmpName FROM Employee WHERE EmpNumber = ?";
		String result = "";
		Connection con = DaoUtil.getConnection();
		PreparedStatement stm = con.prepareStatement(sql);
		stm.setString(1, string);
		ResultSet rs = stm.executeQuery();
		while (rs.next()) {
			result = rs.getString("EmpName");
		}
		DaoUtil.closeConnection(con);
		return result;
	}
}
